package example.service;

import example.dao.connect;
import org.json.JSONException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class dingdan_c {
    public static String dingdan_f(String username) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet p = stmt.executeQuery("select * from sale s , goods g where s.goodsid = g.goodsid and username = '"+username+"' and s.state = '1'");
        return jsonzhuan.resultSetToJson(p);
    }

    public static String dingdan_sale(String username) throws SQLException, JSONException {



        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet p = stmt.executeQuery("select * from sale s , goods g where s.goodsid = g.goodsid and username = '"+username+"' and s.state = '2'");
        return jsonzhuan.resultSetToJson(p);
    }

    public static String dingdan_delect(String username) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet p = stmt.executeQuery("select * from sale s , goods g where s.goodsid = g.goodsid and username = '"+username+"' and s.state = '3'");
        return jsonzhuan.resultSetToJson(p);
    }

    public static String dingdan_realding(String username) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet p = stmt.executeQuery("select * from dingdan where username = '"+username+"'");
        return jsonzhuan.resultSetToJson(p);
    }

    public static void dingdan_d(String ID) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        String sql = "update sale set state = '3' where ID = '"+ID+"'";
        Statement stmt=conn.createStatement();//创建一个Statement对象
        stmt.executeUpdate(sql);//执行sql语句
    }

    public static void dingdan_back(String ID) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        String sql = "update sale set state = '1' where ID = '"+ID+"'";
        Statement stmt=conn.createStatement();//创建一个Statement对象
        stmt.executeUpdate(sql);//执行sql语句
    }

    public static void dingdan_buy(String ID , String address ,String username) throws SQLException, JSONException {
        Connection conn = connect.getConnection();

        String sql4 = "insert into dingdan (saleID , time , realadd , username) values ( '"+ID+"', now() , '"+address+"' , '"+username+"')";
        Statement stmt4=conn.createStatement();//创建一个Statement对象
        stmt4.executeUpdate(sql4);//执行sql语句

        String[] strArray = ID.split(",");
        for ( int i = 0 ; i < strArray.length ; i ++ ){
            String sql = "update sale set state = '2' where ID = '"+strArray[i]+"'";
            Statement stmt=conn.createStatement();//创建一个Statement对象
            stmt.executeUpdate(sql);//执行sql语句

            String sql1 = "update goods set number = (number - (select number1 from sale where ID = '"+strArray[i]+"'))where goodsid = ( select goodsid from sale where ID = '"+strArray[i]+"' )";
            Statement stmt1= conn.createStatement();//创建一个Statement对象
            stmt1.executeUpdate(sql1);//执行sql语句
        }
    }

}
